package sheetOperations;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import Rules.RuleChecker;

import data.ClockInterface;
import data.EmailReminder;
import data.ConfigInfo;
import data.Reference;

import storage.FieldInfo;
import fileIO.CellIO;

public class WorkbookCreator {
	/**
	 * 
	 * @param wkb List of rows to be marked
	 * @param allProfiles skip certain profiles
	 * @return
	 * @throws IOException 
	 * @throws ParseException 
	 */
	public static ArrayList<String> createWB(HashMap<String, ArrayList<Row>>workbookMap, boolean updateReminder) throws IOException, ParseException{
		//Parse each row in the book according to the rules specified in each profile
		//Contains two lists, one for dest one for origin
		ArrayList<String> createdWbList = new ArrayList<String>();
		ArrayList<String> cellType = new ArrayList<String>();
		Object[] keyList = workbookMap.keySet().toArray();		
		Row nRow;
		
		//Loop thru each workbook
		for (int i=0; i<keyList.length; i++){
			String wbName = (String) keyList[i];
			String cneeWb = "DEFAULT";
			ArrayList<Row> rowList = workbookMap.get(wbName);
	        HSSFWorkbook nWorkbook = new HSSFWorkbook();  // or new XSSFWorkbook();
	        HSSFSheet nSheet = nWorkbook.createSheet(wbName);
	        
	        CellStyle cellBordered = nWorkbook.createCellStyle();
	        cellBordered.setBorderBottom((short)1);
	        cellBordered.setBorderLeft((short)1);
	        cellBordered.setBorderRight((short)1);
	        cellBordered.setBorderTop((short)1);
	        
			CellStyle dateFilled = nWorkbook.createCellStyle();
			dateFilled.setFillForegroundColor(Reference.dateCellFilledColor);
			dateFilled.setFillPattern(Reference.dateCellFilledPattern);
			dateFilled.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
	        dateFilled.setBorderBottom((short)1);
	        dateFilled.setBorderLeft((short)1);
	        dateFilled.setBorderRight((short)1);
	        dateFilled.setBorderTop((short)1);
	        
			CellStyle cellFilled = nWorkbook.createCellStyle();
			cellFilled.setFillForegroundColor(Reference.cellFilledColor);
			cellFilled.setFillPattern(Reference.cellFilledPattern);
	        cellFilled.setBorderBottom((short)1);
	        cellFilled.setBorderLeft((short)1);
	        cellFilled.setBorderRight((short)1);
	        cellFilled.setBorderTop((short)1);
	        
			int nrowCount = 0;
			for (int k=0; k<rowList.size();k++){
				Row tempRow = rowList.get(k);
				Iterator<Cell> cellIterator = tempRow.iterator();
				if (k==0){
					int tempCount = 0;
					cellType = new ArrayList<String>();
					nRow = nSheet.createRow(nrowCount);
					while(cellIterator.hasNext()){
						Cell cell = cellIterator.next();
						Cell nCell = nRow.createCell(tempCount);
						nCell.setCellStyle(cellBordered);
						String cellStr = CellIO.ConverttoStrNor(cell);
						nCell.setCellValue(cellStr);
						cellType.add(FieldInfo.getFieldType(cellStr));
						tempCount++;
					}
					nrowCount++;
				}else{
					cneeWb = tempRow.getCell(FieldInfo.getCneeColumn()).toString().toUpperCase();
					System.out.println(wbName + " Row: " + k);
					ArrayList<Integer> highlighter = RuleChecker.ruleHighlight(tempRow);
					if (!highlighter.isEmpty()){
						//Update Reminder count when need to send it out.
						if(updateReminder){
							String tempKey = CellIO.keyGenerator(tempRow);
							EmailReminder.putReminderInfo(tempKey);
						}
						
						int tempCount = 0;
						nRow = nSheet.createRow(nrowCount);
						while(cellIterator.hasNext()){
							Cell cell = cellIterator.next();
							Cell nCell = nRow.createCell(tempCount);
							nCell.setCellStyle(cellBordered);
							if (cell.getCellType()==Cell.CELL_TYPE_BLANK){
								nCell.setCellValue("");
							}else if (cellType.get(tempCount).equals("DATE")){
								nCell.setCellValue(ClockInterface.DateToStr(cell.getDateCellValue()));
							}else{
								nCell.setCellValue(CellIO.ConverttoStr(cell));		
							}
							tempCount++;
						}
						nrowCount++;
						for (int hlCount=0;hlCount<highlighter.size();hlCount++){
							int highlightPosition = highlighter.get(hlCount);
							if(cellType.get(highlightPosition).equals("DATE")){
								nRow.getCell(highlightPosition).setCellStyle(dateFilled);
							}else{
								nRow.getCell(highlightPosition).setCellStyle(cellFilled);
							}
						}
					}
				}
			}
			if(nrowCount > 1){
				//save new workbook as
				String filePath = ConfigInfo.SAVE_DIRECTORY + cneeWb + "-"+ ClockInterface.getTodayDate("MMdd") + "\\";
				
				boolean exists = (new File(filePath)).exists();
				if(!exists){
					boolean success = (new File(filePath)).mkdir();
					System.out.println(success);
				}
				
				if (wbName.contains("2nd Reminder"))
					filePath = filePath + "2nd Reminder\\";
				else if(wbName.contains("3rd Reminder"))
					filePath = filePath + "3rd Reminder\\";

				exists = (new File(filePath)).exists();
				if(!exists){
					boolean success = (new File(filePath)).mkdir();
					System.out.println(success);
				}
				
				String fileFullName = filePath + wbName + "-" + ClockInterface.getTodayDate("MMddyy") + ".xls";

		        FileOutputStream fileOut = new FileOutputStream(fileFullName);
		 
		        nWorkbook.write(fileOut);
		        fileOut.close();
		        createdWbList.add(fileFullName);
			}
		}
		return createdWbList;
	}
	public static ArrayList<String> createWBnoRule(HashMap<String, ArrayList<Row>>workbookMap, boolean updateReminder) throws IOException, ParseException{
		//Parse each row in the book according to the rules specified in each profile
		//Contains two lists, one for dest one for origin
		ArrayList<String> createdWbList = new ArrayList<String>();
		ArrayList<String> cellType = new ArrayList<String>();
		Object[] keyList = workbookMap.keySet().toArray();		
		Row nRow;
		
		//Loop thru each workbook
		for (int i=0; i<keyList.length; i++){
			String wbName = (String) keyList[i];
			String cneeWb = "DEFAULT";
			ArrayList<Row> rowList = workbookMap.get(wbName);
	        HSSFWorkbook nWorkbook = new HSSFWorkbook();  // or new XSSFWorkbook();
	        HSSFSheet nSheet = nWorkbook.createSheet(wbName);
	        
	        CellStyle cellBordered = nWorkbook.createCellStyle();
	        cellBordered.setBorderBottom((short)1);
	        cellBordered.setBorderLeft((short)1);
	        cellBordered.setBorderRight((short)1);
	        cellBordered.setBorderTop((short)1);
	    
			int nrowCount = 0;
			for (int k=0; k<rowList.size();k++){
				Row tempRow = rowList.get(k);
				Iterator<Cell> cellIterator = tempRow.iterator();
				if (k==0){
					int tempCount = 0;
					cellType = new ArrayList<String>();
					nRow = nSheet.createRow(nrowCount);
					while(cellIterator.hasNext()){
						Cell cell = cellIterator.next();
						Cell nCell = nRow.createCell(tempCount);
						nCell.setCellStyle(cellBordered);
						String cellStr = CellIO.ConverttoStrNor(cell);
						nCell.setCellValue(cellStr);
						cellType.add(FieldInfo.getFieldType(cellStr));
						tempCount++;
					}
					nrowCount++;
				}else{
					cneeWb = tempRow.getCell(FieldInfo.getCneeColumn()).toString().toUpperCase();
					System.out.println(wbName + " Row: " + k);
					//Update Reminder count when need to send it out.
					if(updateReminder){
						String tempKey = CellIO.keyGenerator(tempRow);
						EmailReminder.putReminderInfo(tempKey);
					}
					
					int tempCount = 0;
					nRow = nSheet.createRow(nrowCount);
					while(cellIterator.hasNext()){
						Cell cell = cellIterator.next();
						Cell nCell = nRow.createCell(tempCount);
						nCell.setCellStyle(cellBordered);
						if (cell.getCellType()==Cell.CELL_TYPE_BLANK){
							nCell.setCellValue("");
						}else if (cellType.get(tempCount).equals("DATE")){
							nCell.setCellValue(ClockInterface.DateToStr(cell.getDateCellValue()));
						}else{
							nCell.setCellValue(CellIO.ConverttoStr(cell));		
						}
						tempCount++;
					}
					nrowCount++;
				}
			}
			if(nrowCount > 1){
				//save new workbook as
				String filePath = ConfigInfo.SAVE_DIRECTORY + cneeWb + "-"+ ClockInterface.getTodayDate("MMdd") + "\\";
				
				boolean exists = (new File(filePath)).exists();
				if(!exists){
					boolean success = (new File(filePath)).mkdir();
					System.out.println(success);
				}
				
				if (wbName.contains("2nd Reminder"))
					filePath = filePath + "2nd Reminder\\";
				else if(wbName.contains("3rd Reminder"))
					filePath = filePath + "3rd Reminder\\";

				exists = (new File(filePath)).exists();
				if(!exists){
					boolean success = (new File(filePath)).mkdir();
					System.out.println(success);
				}
				
				String fileFullName = filePath + wbName + "-" + ClockInterface.getTodayDate("MMddyy") + ".xls";

		        FileOutputStream fileOut = new FileOutputStream(fileFullName);
		 
		        nWorkbook.write(fileOut);
		        fileOut.close();
		        createdWbList.add(fileFullName);
			}
		}
		return createdWbList;
	}
}
